package cn.kc.jdbc.dao;

import cn.kc.jdbc.entity.Score;
import cn.kc.jdbc.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * @author mosin
 * date 2022/2/14
 * @version 1.0
 */
public class ScoreDaoImpl implements  ScoreDao {


    @Override
    public Integer addScore(long id, long courseId, Double score) {

        DataSource dataSource = DruidUtil.getDataSource();
        QueryRunner runner = new QueryRunner(dataSource);
        String  sql = "insert into score (stu_id,subject_id,score)values(?,?,?)";
        try {
            int line = runner.update(sql, id, courseId, score);
            return line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Score> findAllScores(String courseName) {

        DataSource dataSource = DruidUtil.getDataSource();
        QueryRunner runner = new QueryRunner(dataSource);

        String sql = "SELECT stu.stu_name as stuName , course.course_name as courseName, scr.score  " +
                "FROM score scr LEFT JOIN  stu ON scr.stu_id = stu.id" +
                " LEFT JOIN course ON scr.subject_id = course.course_id " +
                "WHERE course.course_name = ? " +
                "ORDER BY scr.score DESC";
        try {
            List<Score> scores = runner.query(sql, new BeanListHandler<>(Score.class), courseName);
            return  scores;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
